using System;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using THANHTUNGO.Portal.Components.Helpers;

namespace THANHTUNGO.Portal.Components.Security
{
    public enum UserStatus
    {
        ACTIVE,
        DEACTIVE,
        LOCK
    }

    public partial class User
    {
        public override string ToString()
        {
            return this._Name + "|" + this._Email + "|" + this._ID;
        }

        public static DataSet SelectBy_RoleID(string roleID)
        {
            if (roleID == "ALL")
            {
                return SelectAll();
            }

            string spName = "p_SYSTEM_User_SelectBy_RoleID";
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@RoleID", DbType.String, roleID);

            return db.ExecuteDataSet(dbCommand);
        }

        public static DataSet SelectBy_ID(int id)
        {
            string spName = "p_SYSTEM_User_Load";
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand(spName);
            db.AddInParameter(dbCommand, "@ID", DbType.Int32, id);
            return db.ExecuteDataSet(dbCommand);
        }

        public static DataSet SelectBy_Email(string email)
        {
            string spName = "p_SYSTEM_User_SelectBy_Email";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(spName);
            db.AddInParameter(dbCommand, "@Email", DbType.String, email);
            return db.ExecuteDataSet(dbCommand);
        }

        public static bool CheckExist(string email)
        {
            string spName = "p_SYSTEM_User_CheckExistBy_Email";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(spName);
            db.AddInParameter(dbCommand, "@Email", DbType.String, email);
            IDataReader reader = db.ExecuteReader(dbCommand);
            bool returnValue = false;
            if (reader.Read())
            {
                returnValue = true;
            }
            reader.Close();
            dbCommand.Connection.Close();
            return returnValue;
        }

        public static string GetStatus(string email)
        {
            string returnValue = UserStatus.ACTIVE.ToString();
            string spName = "p_SYSTEM_User_GetStatusBy_Email";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(spName);
            db.AddInParameter(dbCommand, "@Email", DbType.String, email);
            IDataReader reader = db.ExecuteReader(dbCommand);
            if (reader.Read())
            {
                returnValue = reader["Status"].ToString();
            }
            reader.Close();
            dbCommand.Connection.Close();
            return returnValue;
        }


        public static string ResetPassword(string email)
        {
            string spName = "p_SYSTEM_User_ResetPassword";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(spName);

            // Generate new password.
            Random rnd = new Random();
            string newPassword = string.Empty;
            for (int n = 0; n < 8; n++)
            {
                newPassword += rnd.Next(0, 9).ToString();
            }
            db.AddInParameter(dbCommand, "@NewPassword", DbType.String, PortalSecurity.Encrypt(newPassword));
            db.AddInParameter(dbCommand, "@Email", DbType.String, email);
            int i = db.ExecuteNonQuery(dbCommand);
            if (i <= 0) newPassword = string.Empty;
            return newPassword;
        }

        public int Register()
        {
            string spName = "p_SYSTEM_User_Register";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(spName);
            db.AddInParameter(dbCommand, "@Name", DbType.String, this._Name);
            db.AddInParameter(dbCommand, "@Email", DbType.String, this._Email);
            db.AddInParameter(dbCommand, "@Password", DbType.String, PortalSecurity.Encrypt(this._Password));            
            db.AddOutParameter(dbCommand, "@ID", DbType.Int32, 4);
            db.ExecuteNonQuery(dbCommand);
            return (int) db.GetParameterValue(dbCommand, "@ID");
        }

        public static DataSet Search(string whereExpression)
        {
            string query = "SELECT * FROM t_SYSTEM_Users WHERE " + whereExpression;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(query);
            return db.ExecuteDataSet(dbCommand);
        }

        //-----------------------------------------------------------------------------------------
        public static bool Active(string activeCode)
        {
            try
            {
                string spName = "p_SYSTEM_User_Active";
                Database db = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = db.GetStoredProcCommand(spName);
                db.AddInParameter(dbCommand, "@Email", DbType.String, DecryptHelper.Decrypt(activeCode));
                int i = db.ExecuteNonQuery(dbCommand);
                return i > 0;
            }
            catch
            {
                return false;
            }
        }

        public static void UpdateStatus(int id, UserStatus status)
        {
            string spName = "p_SYSTEM_User_UpdateStatus";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(spName);
            db.AddInParameter(dbCommand, "@Status", DbType.String, status.ToString());
            db.AddInParameter(dbCommand, "@ID", DbType.Int32, id);
            db.ExecuteNonQuery(dbCommand);
        }

        public static bool ChangePassword(string email, string oldPassword, string newPassword)
        {
            string spName = "p_SYSTEM_User_ChangePassword";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(spName);
            db.AddInParameter(dbCommand, "@NewPassword", DbType.String, PortalSecurity.Encrypt(newPassword));
            db.AddInParameter(dbCommand, "@Email", DbType.String, email);
            db.AddInParameter(dbCommand, "@OldPassword", DbType.String, PortalSecurity.Encrypt(oldPassword));
            int i = db.ExecuteNonQuery(dbCommand);
            return i > 0;
        }

        public static bool AddToRole(int userID, string roleID)
        {
            string spName = "p_SYSTEM_User_Role_Insert";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(spName);
            db.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
            db.AddInParameter(dbCommand, "@RoleID", DbType.String, roleID);
            int i = db.ExecuteNonQuery(dbCommand);
            return i > 0;
        }


        public static void AddToRole(int userID, int roleID)
        {
            AddToRole(null, userID, roleID);
        }


        private static void DeleteRole(DbTransaction transaction, int userID)
        {
            string spName = "p_SYSTEM_Users_Roles_DeleteBy_UserID";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);
            db.AddInParameter(dbCommand, "@UserID", SqlDbType.Int, userID);
            db.ExecuteNonQuery(dbCommand, transaction);
        }

        public static int RemoveRole(int userID, int roleID)
        {
            string spName = "p_SYSTEM_Users_Roles_DeleteBy_UserID_RoleID";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);
            db.AddInParameter(dbCommand, "@UserID", SqlDbType.Int, userID);
            db.AddInParameter(dbCommand, "@RoleID", SqlDbType.Int, roleID);
            return db.ExecuteNonQuery(dbCommand);

        }

        private static void AddToRole(DbTransaction transaction, int userID, int roleID)
        {
            string spName = "p_SYSTEM_User_AddToRole";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@UserID", SqlDbType.Int, userID);
            db.AddInParameter(dbCommand, "@RoleID", SqlDbType.Int, roleID);

            if (transaction != null)
                db.ExecuteNonQuery(dbCommand, transaction);
            else
                db.ExecuteNonQuery(dbCommand);
        }


        public static bool AddToRoles(int userID, ArrayList roles)
        {
            bool returnValue;
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                {
                    try
                    {
                        // Clear all role of user before insert new role.
                        DeleteRole(transaction, userID);

                        foreach (string role in roles)
                        {
                            AddToRole(transaction, userID, Convert.ToInt32(role));
                        }
                        transaction.Commit();
                        returnValue = true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        returnValue = false;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return returnValue;
        }

        public bool Load(string email)
        {
            string spName = "p_SYSTEM_User_SelectByEmail";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@Email", DbType.String, email);

            IDataReader reader = db.ExecuteReader(dbCommand);

            bool returnValue = false;
            if (reader.Read())
            {
                if (!reader.IsDBNull(reader.GetOrdinal("ID"))) this._ID = reader.GetInt32(reader.GetOrdinal("ID"));
                if (!reader.IsDBNull(reader.GetOrdinal("GroupID"))) this._ID = reader.GetInt32(reader.GetOrdinal("GroupID"));
                if (!reader.IsDBNull(reader.GetOrdinal("Name"))) this._Name = reader.GetString(reader.GetOrdinal("Name"));
                if (!reader.IsDBNull(reader.GetOrdinal("Password"))) this._Password = reader.GetString(reader.GetOrdinal("Password"));
                if (!reader.IsDBNull(reader.GetOrdinal("Email"))) this._Email = reader.GetString(reader.GetOrdinal("Email"));
                returnValue = true;
            }

            reader.Close();
            dbCommand.Connection.Close();

            return returnValue;
        }

        //---------------------------------------------------------------------------------------------

        public static DataSet GetRolesByEmail(string email)
        {
            string spName = "p_SYSTEM_User_GetRoles";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@Email", SqlDbType.VarChar, email);

            return db.ExecuteDataSet(dbCommand);
        }

        public static string[] GetRoles(string email)
        {
            string spName = "p_SYSTEM_User_GetRolesBy_Email";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@Email", DbType.String, email);

            IDataReader reader = db.ExecuteReader(dbCommand);

            ArrayList userRoles = new ArrayList();
            while (reader.Read())
            {
                userRoles.Add(reader["ID"].ToString());
            }

            reader.Close();
            dbCommand.Connection.Close();
            return (string[]) userRoles.ToArray(typeof (string));
        }

        public static string[] GetRoles(int userID)
        {
            string spName = "p_SYSTEM_User_GetRolesBy_UserID";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@UserID", DbType.String, userID);

            IDataReader reader = db.ExecuteReader(dbCommand);

            ArrayList userRoles = new ArrayList();
            while (reader.Read())
            {
                userRoles.Add(reader["ID"].ToString());
            }

            reader.Close();
            dbCommand.Connection.Close();
            return (string[])userRoles.ToArray(typeof(string));
        }
        /// <summary>
        /// Get roles of user ID.
        /// </summary>
        /// <param name="userID">ID of user</param>
        /// <returns>DataSet</returns>
        public static DataSet SelectRolesBy_UserID(int userID)
        {
            string spName = "p_SYSTEM_User_GetRolesBy_UserID";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(spName);
            db.AddInParameter(dbCommand, "@UserID", DbType.String, userID);
            return db.ExecuteDataSet(dbCommand);
        }

        //-----------------------------------------------------------------------------------------

        /// <summary>
        /// Check exist of Email. Return false if Email exists; true if Email doesn't exist
        /// </summary>
        /// <param name="email">Email of employee</param>
        /// <returns>boolean</returns>
        public static bool CheckEmail(string email)
        {
            bool returnValue = true;
            string spName = "p_SYSTEM_User_CheckEmail";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);
            db.AddInParameter(dbCommand, "@Email", DbType.String, email);
            IDataReader reader = db.ExecuteReader(dbCommand);
            if (reader.Read())
            {
                returnValue = false;
            }
            reader.Close();
            dbCommand.Connection.Close();
            return returnValue;
        }

        public static IdentityUser Login(string email, string password)
        {
            string spName = "p_SYSTEM_User_Login";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(spName);
            db.AddInParameter(dbCommand, "@Email", DbType.String, email);
            db.AddInParameter(dbCommand, "@Password", DbType.String, PortalSecurity.Encrypt(password));
            IDataReader reader = db.ExecuteReader(dbCommand);
            IdentityUser returnValue = null;
            if (reader.Read())
            {
                returnValue = new IdentityUser(reader["ID"].ToString(), "1", reader["Email"].ToString());
            }
            reader.Close();
            dbCommand.Connection.Close();
            return returnValue;
        }

        //-----------------------------------------------------------------------------------------

        public static int ChangeDepartment(int userID, string departmentID)
        {
            string spName = "p_SYSTEM_User_ChangeDepartment";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@ID", SqlDbType.Int, userID);
            db.AddInParameter(dbCommand, "@DepartmentID", SqlDbType.VarChar, departmentID);
            return db.ExecuteNonQuery(dbCommand);
        }
    }
}